{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Making Pandas DataFrames from API Requests\n",
    "In this example, we will use the U.S. Geological Survey's API to grab a JSON object of earthquake data and convert it to a pandas DataFrame.\n",
    "\n",
    "USGS API: https://earthquake.usgs.gov/fdsnws/event/1/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Data from API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "200\n"
     ]
    }
   ],
   "source": [
    "import datetime\n",
    "import pandas as pd\n",
    "import requests\n",
    "\n",
    "yesterday = datetime.date.today() - datetime.timedelta(days=1)\n",
    "api = 'https://earthquake.usgs.gov/fdsnws/event/1/query'\n",
    "payload = {\n",
    "    'format' : 'geojson',\n",
    "    'starttime' : yesterday - datetime.timedelta(days=26),\n",
    "    'endtime' : yesterday\n",
    "}\n",
    "response = requests.get(api, params=payload)\n",
    "\n",
    "# let's make sure the request was OK\n",
    "print(response.status_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Response of 200 means OK, so we can pull the data out of the result. Since we asked the API for a JSON, we can extract it from the response with the JSON method."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Isolate the Data from the JSON Response\n",
    "We need to check the structures of the response data to know where our data is."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['type', 'metadata', 'features', 'bbox'])"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "earthquake_json = response.json()\n",
    "earthquake_json.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'generated': 1540142204000,\n",
       " 'url': 'https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&starttime=2018-09-17&endtime=2018-10-13',\n",
       " 'title': 'USGS Earthquakes',\n",
       " 'status': 200,\n",
       " 'api': '1.5.8',\n",
       " 'count': 9903}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "earthquake_json['metadata']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Each element in the JSON array `features` is a row of data for our dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(earthquake_json['features'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your data will be different depending on the date you run this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'type': 'Feature',\n",
       " 'properties': {'mag': 1.5,\n",
       "  'place': '123km WNW of Arctic Village, Alaska',\n",
       "  'time': 1539387977830,\n",
       "  'updated': 1539993599455,\n",
       "  'tz': -540,\n",
       "  'url': 'https://earthquake.usgs.gov/earthquakes/eventpage/ak20279829',\n",
       "  'detail': 'https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=ak20279829&format=geojson',\n",
       "  'felt': None,\n",
       "  'cdi': None,\n",
       "  'mmi': None,\n",
       "  'alert': None,\n",
       "  'status': 'reviewed',\n",
       "  'tsunami': 0,\n",
       "  'sig': 35,\n",
       "  'net': 'ak',\n",
       "  'code': '20279829',\n",
       "  'ids': ',ak20279829,',\n",
       "  'sources': ',ak,',\n",
       "  'types': ',geoserve,origin,phase-data,',\n",
       "  'nst': None,\n",
       "  'dmin': None,\n",
       "  'rms': 0.46,\n",
       "  'gap': None,\n",
       "  'magType': 'ml',\n",
       "  'type': 'earthquake',\n",
       "  'title': 'M 1.5 - 123km WNW of Arctic Village, Alaska'},\n",
       " 'geometry': {'type': 'Point', 'coordinates': [-148.3878, 68.4597, 17.8]},\n",
       " 'id': 'ak20279829'}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "earthquake_json['features'][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert to DataFrame\n",
    "We need to grab the `properties` section out of every entry in the `features` JSON array to create our dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>alert</th>\n",
       "      <th>cdi</th>\n",
       "      <th>code</th>\n",
       "      <th>detail</th>\n",
       "      <th>dmin</th>\n",
       "      <th>felt</th>\n",
       "      <th>gap</th>\n",
       "      <th>ids</th>\n",
       "      <th>mag</th>\n",
       "      <th>magType</th>\n",
       "      <th>...</th>\n",
       "      <th>sources</th>\n",
       "      <th>status</th>\n",
       "      <th>time</th>\n",
       "      <th>title</th>\n",
       "      <th>tsunami</th>\n",
       "      <th>type</th>\n",
       "      <th>types</th>\n",
       "      <th>tz</th>\n",
       "      <th>updated</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20279829</td>\n",
       "      <td>https://earthquake.usgs.gov/fdsnws/event/1/que...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>,ak20279829,</td>\n",
       "      <td>1.50</td>\n",
       "      <td>ml</td>\n",
       "      <td>...</td>\n",
       "      <td>,ak,</td>\n",
       "      <td>reviewed</td>\n",
       "      <td>1539387977830</td>\n",
       "      <td>M 1.5 - 123km WNW of Arctic Village, Alaska</td>\n",
       "      <td>0</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>,geoserve,origin,phase-data,</td>\n",
       "      <td>-540.0</td>\n",
       "      <td>1539993599455</td>\n",
       "      <td>https://earthquake.usgs.gov/earthquakes/eventp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20279827</td>\n",
       "      <td>https://earthquake.usgs.gov/fdsnws/event/1/que...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>,ak20279827,</td>\n",
       "      <td>1.80</td>\n",
       "      <td>ml</td>\n",
       "      <td>...</td>\n",
       "      <td>,ak,</td>\n",
       "      <td>reviewed</td>\n",
       "      <td>1539387643408</td>\n",
       "      <td>M 1.8 - 72km NNW of Arctic Village, Alaska</td>\n",
       "      <td>0</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>,geoserve,origin,phase-data,</td>\n",
       "      <td>-540.0</td>\n",
       "      <td>1539993598904</td>\n",
       "      <td>https://earthquake.usgs.gov/earthquakes/eventp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>61437241</td>\n",
       "      <td>https://earthquake.usgs.gov/fdsnws/event/1/que...</td>\n",
       "      <td>0.17560</td>\n",
       "      <td>NaN</td>\n",
       "      <td>335.0</td>\n",
       "      <td>,uw61437241,</td>\n",
       "      <td>1.49</td>\n",
       "      <td>ml</td>\n",
       "      <td>...</td>\n",
       "      <td>,uw,</td>\n",
       "      <td>reviewed</td>\n",
       "      <td>1539387421260</td>\n",
       "      <td>M 1.5 Explosion - 2km N of Hockinson, Washington</td>\n",
       "      <td>0</td>\n",
       "      <td>explosion</td>\n",
       "      <td>,geoserve,origin,phase-data,</td>\n",
       "      <td>-480.0</td>\n",
       "      <td>1539631055160</td>\n",
       "      <td>https://earthquake.usgs.gov/earthquakes/eventp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20279823</td>\n",
       "      <td>https://earthquake.usgs.gov/fdsnws/event/1/que...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>,ak20279823,</td>\n",
       "      <td>2.20</td>\n",
       "      <td>ml</td>\n",
       "      <td>...</td>\n",
       "      <td>,ak,</td>\n",
       "      <td>reviewed</td>\n",
       "      <td>1539387411152</td>\n",
       "      <td>M 2.2 - 78km SSW of Kaktovik, Alaska</td>\n",
       "      <td>0</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>,geoserve,origin,phase-data,</td>\n",
       "      <td>-540.0</td>\n",
       "      <td>1539993598323</td>\n",
       "      <td>https://earthquake.usgs.gov/earthquakes/eventp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37387882</td>\n",
       "      <td>https://earthquake.usgs.gov/fdsnws/event/1/que...</td>\n",
       "      <td>0.01668</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45.0</td>\n",
       "      <td>,ci37387882,</td>\n",
       "      <td>1.14</td>\n",
       "      <td>ml</td>\n",
       "      <td>...</td>\n",
       "      <td>,ci,</td>\n",
       "      <td>reviewed</td>\n",
       "      <td>1539387294800</td>\n",
       "      <td>M 1.1 - 9km NE of Aguanga, CA</td>\n",
       "      <td>0</td>\n",
       "      <td>earthquake</td>\n",
       "      <td>,focal-mechanism,geoserve,nearby-cities,origin...</td>\n",
       "      <td>-480.0</td>\n",
       "      <td>1539613956040</td>\n",
       "      <td>https://earthquake.usgs.gov/earthquakes/eventp...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  alert  cdi      code                                             detail  \\\n",
       "0  None  NaN  20279829  https://earthquake.usgs.gov/fdsnws/event/1/que...   \n",
       "1  None  NaN  20279827  https://earthquake.usgs.gov/fdsnws/event/1/que...   \n",
       "2  None  NaN  61437241  https://earthquake.usgs.gov/fdsnws/event/1/que...   \n",
       "3  None  NaN  20279823  https://earthquake.usgs.gov/fdsnws/event/1/que...   \n",
       "4  None  NaN  37387882  https://earthquake.usgs.gov/fdsnws/event/1/que...   \n",
       "\n",
       "      dmin  felt    gap           ids   mag magType  \\\n",
       "0      NaN   NaN    NaN  ,ak20279829,  1.50      ml   \n",
       "1      NaN   NaN    NaN  ,ak20279827,  1.80      ml   \n",
       "2  0.17560   NaN  335.0  ,uw61437241,  1.49      ml   \n",
       "3      NaN   NaN    NaN  ,ak20279823,  2.20      ml   \n",
       "4  0.01668   NaN   45.0  ,ci37387882,  1.14      ml   \n",
       "\n",
       "                         ...                          sources    status  \\\n",
       "0                        ...                             ,ak,  reviewed   \n",
       "1                        ...                             ,ak,  reviewed   \n",
       "2                        ...                             ,uw,  reviewed   \n",
       "3                        ...                             ,ak,  reviewed   \n",
       "4                        ...                             ,ci,  reviewed   \n",
       "\n",
       "            time                                             title  tsunami  \\\n",
       "0  1539387977830       M 1.5 - 123km WNW of Arctic Village, Alaska        0   \n",
       "1  1539387643408        M 1.8 - 72km NNW of Arctic Village, Alaska        0   \n",
       "2  1539387421260  M 1.5 Explosion - 2km N of Hockinson, Washington        0   \n",
       "3  1539387411152              M 2.2 - 78km SSW of Kaktovik, Alaska        0   \n",
       "4  1539387294800                     M 1.1 - 9km NE of Aguanga, CA        0   \n",
       "\n",
       "         type                                              types     tz  \\\n",
       "0  earthquake                       ,geoserve,origin,phase-data, -540.0   \n",
       "1  earthquake                       ,geoserve,origin,phase-data, -540.0   \n",
       "2   explosion                       ,geoserve,origin,phase-data, -480.0   \n",
       "3  earthquake                       ,geoserve,origin,phase-data, -540.0   \n",
       "4  earthquake  ,focal-mechanism,geoserve,nearby-cities,origin... -480.0   \n",
       "\n",
       "         updated                                                url  \n",
       "0  1539993599455  https://earthquake.usgs.gov/earthquakes/eventp...  \n",
       "1  1539993598904  https://earthquake.usgs.gov/earthquakes/eventp...  \n",
       "2  1539631055160  https://earthquake.usgs.gov/earthquakes/eventp...  \n",
       "3  1539993598323  https://earthquake.usgs.gov/earthquakes/eventp...  \n",
       "4  1539613956040  https://earthquake.usgs.gov/earthquakes/eventp...  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "earthquake_properties_data = [data['properties'] for data in earthquake_json['features']]\n",
    "df = pd.DataFrame(earthquake_properties_data)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### (Optional) Write Data to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.DataFrame(earthquake_properties_data).to_csv('earthquakes.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
